from pymysql import Connection
from datetime import datetime


# 数据库连接
def connect():
    """
    创建并返回一个MySQL数据库连接对象

    返回:
        Connection: 数据库连接对象，配置了主机、端口、用户、密码、数据库和字符集等参数
        设置autocommit=True确保每次操作后自动提交事务
    """
    return Connection(
        host='localhost', port=3306, user='root',
        passwd='root', database='db3', charset='utf8', autocommit=True
    )


# 创建表并初始化
def createTable():
    """
    创建银行表和银行卡表，如果表不存在则创建，并初始化银行数据

    功能:
        1. 创建t_rank表（银行表）存储银行信息
        2. 创建t_card表（银行卡表）存储银行卡信息
        3. 如果银行表中没有数据，则插入初始银行数据（五大银行）
    """
    db = connect()  # 获取数据库连接
    cursor = db.cursor()  # 创建游标对象用于执行SQL语句

    # 创建银行表（如果不存在）
    cursor.execute("""
        create table if not exists t_rank(
            id int primary key auto_increment,
            r_name varchar(20))
    """)

    # 创建银行卡表（如果不存在）
    cursor.execute("""
        create table if not exists t_card(
            id int primary key auto_increment,
            username varchar(20),
            card_no  varchar(20),
            create_time datetime,
            money double,
            r_id int)
    """)

    # 初始化银行数据（如果表中没有数据）
    cursor.execute("SELECT COUNT(*) FROM t_rank")  # 查询银行表记录数
    if cursor.fetchone()[0] == 0:  # 如果记录数为0（表为空）
        banks = ['工商银行', '建设银行', '农业银行', '中国银行', '招商银行']  # 预定义银行列表
        for bank in banks:
            # 插入银行数据
            cursor.execute("INSERT INTO t_rank (r_name) VALUES (%s)", (bank,))

    db.close()  # 关闭数据库连接


# 添加银行卡
def add_card(username, card_no, money, r_id):
    """
    向数据库中添加一张新的银行卡

    参数:
        username (str): 用户名
        card_no (str): 银行卡号
        money (float): 初始金额
        r_id (int): 银行ID（关联t_rank表）
    """
    db = connect()  # 获取数据库连接
    cursor = db.cursor()  # 创建游标对象
    # 执行插入操作
    cursor.execute(
        "INSERT INTO t_card (username, card_no, create_time, money, r_id) VALUES (%s, %s, %s, %s, %s)",
        (username, card_no, datetime.now(), money, r_id)  # 使用当前时间作为创建时间
    )
    db.close()  # 关闭数据库连接
    print("添加成功!")  # 提示用户操作成功


# 通用查询函数
def query(sql, params=None):
    """
    执行SQL查询并返回结果

    参数:
        sql (str): 要执行的SQL查询语句
        params (tuple, optional): SQL参数，用于防止SQL注入

    返回:
        list: 查询结果列表，每个元素是一个元组表示一行数据
    """
    db = connect()  # 获取数据库连接
    cursor = db.cursor()  # 创建游标对象
    cursor.execute(sql, params)  # 执行SQL查询
    result = cursor.fetchall()  # 获取所有结果
    db.close()  # 关闭数据库连接
    return result  # 返回查询结果


# 显示结果
def show_results(rows, title):
    """
    格式化显示查询结果

    参数:
        rows (list): 查询结果列表
        title (str): 显示标题
    """
    print(f"\n===== {title} =====")  # 打印标题
    if not rows:  # 如果没有结果
        print("没有找到记录!")  # 提示用户
        return

    # 打印表头（简化显示，实际应用中可能需要更复杂的格式化）
    print("ID", "用户名", "卡号", "开卡时间", "余额", "银行")
    for row in rows:
        # 打印每一行数据，使用制表符分隔
        print(f"{row[0]}\t{row[1]}\t{row[2]}\t{row[3]}\t{row[4]}\t{row[5]}")


# 功能函数
def findAll():
    """
    查询并显示所有银行卡信息，包括关联的银行名称
    """
    # 执行多表连接查询，获取银行卡及其对应的银行信息
    rows = query("""
                 SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                 FROM t_card c
                          JOIN t_rank r ON c.r_id = r.id
                 ORDER BY c.id
                 """)
    show_results(rows, "所有银行卡信息")  # 显示结果


def add_card_interactive():
    """
    交互式添加银行卡功能，从用户输入获取信息并调用add_card函数
    """
    username = input("请输入用户名: ")  # 获取用户名
    card_no = input("请输入卡号: ")  # 获取卡号
    money = float(input("请输入初始金额: "))  # 获取初始金额

    # 显示银行列表供用户选择
    banks = query("SELECT id, r_name FROM t_rank")
    for bank in banks:
        print(f"{bank[0]}. {bank[1]}")  # 显示银行ID和名称

    r_id = int(input("请选择银行ID: "))  # 获取用户选择的银行ID
    add_card(username, card_no, money, r_id)  # 调用添加银行卡函数


def update_card_money():
    """
    更新指定银行卡的余额
    """
    card_id = int(input("请输入银行卡ID: "))  # 获取要更新的银行卡ID
    new_money = float(input("请输入新金额: "))  # 获取新金额

    db = connect()  # 获取数据库连接
    cursor = db.cursor()  # 创建游标对象
    # 执行UPDATE语句更新金额
    affected_rows = cursor.execute("UPDATE t_card SET money = %s WHERE id = %s", (new_money, card_id))
    db.close()  # 关闭数据库连接

    if affected_rows > 0:  # 如果影响了行数（更新成功）
        print("修改成功!")
    else:
        print("未找到银行卡!")  # 如果没有找到对应的银行卡


def find_max_balance():
    """
    查询并显示余额最多的银行卡信息
    """
    # 按余额降序排序，只取第一条记录（余额最多）
    row = query("""
                SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                FROM t_card c
                         JOIN t_rank r ON c.r_id = r.id
                ORDER BY c.money DESC LIMIT 1
                """)
    show_results(row, "余额最多的银行卡")  # 显示结果


def find_cards_by_bank():
    """
    按银行查询银行卡信息，并计算该银行所有卡的总余额
    """
    # 显示银行列表供用户选择
    banks = query("SELECT id, r_name FROM t_rank")
    for bank in banks:
        print(f"{bank[0]}. {bank[1]}")  # 显示银行ID和名称

    bank_id = int(input("请选择银行ID: "))  # 获取用户选择的银行ID
    # 查询指定银行的所有银行卡
    rows = query("SELECT card_no, money FROM t_card WHERE r_id = %s", (bank_id,))

    total = sum(row[1] for row in rows)  # 计算总余额
    for row in rows:
        print(f"卡号: {row[0]}, 余额: {row[1]}")  # 显示每张卡的卡号和余额
    print(f"总余额: {total}")  # 显示总余额


def find_cards_by_balance_range():
    """
    按余额范围查询银行卡信息
    """
    min_bal = float(input("最小余额: "))  # 获取最小余额
    max_bal = float(input("最大余额: "))  # 获取最大余额

    # 查询余额在指定范围内的银行卡
    rows = query("""
                 SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                 FROM t_card c
                          JOIN t_rank r ON c.r_id = r.id
                 WHERE c.money BETWEEN %s AND %s
                 ORDER BY c.money
                 """, (min_bal, max_bal))
    show_results(rows, f"余额范围 {min_bal}-{max_bal} 的银行卡")  # 显示结果


def find_cards_by_date_range():
    """
    按开卡日期范围查询银行卡信息
    """
    start = input("开始日期(YYYY-MM-DD): ")  # 获取开始日期
    end = input("结束日期(YYYY-MM-DD): ")  # 获取结束日期

    # 查询创建时间在指定日期范围内的银行卡
    # 注意：结束日期添加了时间部分，以包含当天的所有记录
    rows = query("""
                 SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                 FROM t_card c
                          JOIN t_rank r ON c.r_id = r.id
                 WHERE c.create_time BETWEEN %s AND %s
                 ORDER BY c.create_time
                 """, (start, end + " 23:59:59"))
    show_results(rows, f"开卡日期 {start} 到 {end} 的银行卡")  # 显示结果


def find_cards_by_name():
    """
    按用户名模糊查询银行卡信息
    """
    name = input("请输入姓名: ")  # 获取要查询的姓名（或部分姓名）
    # 使用LIKE进行模糊查询，%表示任意字符
    rows = query("""
                 SELECT c.id, c.username, c.card_no, c.create_time, c.money, r.r_name
                 FROM t_card c
                          JOIN t_rank r ON c.r_id = r.id
                 WHERE c.username LIKE %s
                 ORDER BY c.username
                 """, (f"%{name}%",))  # 在姓名前后添加%实现模糊匹配
    show_results(rows, f"姓名包含 '{name}' 的银行卡")  # 显示结果


# 主菜单
def main_menu():
    """
    显示主菜单并处理用户选择，提供银行卡管理系统的各种功能
    """
    while True:  # 循环显示菜单直到用户选择退出
        print("\n银行卡管理系统")
        print("1.浏览所有银行卡 2.添加银行卡 3.修改金额")
        print("4.余额最多卡 5.按银行查询 6.按余额查询")
        print("7.按日期查询 8.按姓名查询 0.退出")

        choice = input("请选择: ")  # 获取用户选择
        # 定义选项字典，映射选择到对应的函数
        options = {
            '1': findAll, '2': add_card_interactive, '3': update_card_money,
            '4': find_max_balance, '5': find_cards_by_bank, '6': find_cards_by_balance_range,
            '7': find_cards_by_date_range, '8': find_cards_by_name, '0': exit
        }

        if choice in options:  # 如果选择有效
            if choice == '0':  # 如果选择退出
                print("再见!")
                break  # 退出循环
            options[choice]()  # 调用对应的功能函数
        else:
            print("无效选择!")  # 提示用户选择无效


# 启动
if __name__ == '__main__':
    """
    程序入口点，首先创建必要的表，然后启动主菜单
    """
    createTable()  # 创建表（如果不存在）
    main_menu()  # 启动主菜单